package com.vecspace.database.dao;

import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.rowset.serial.SerialClob;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;

import com.vecspace.data.model.IPagination;
import com.vecspace.database.model.DataTable;
import com.vecspace.database.model.QueryResult;
import com.vecspace.utils.Convert;

import comvecspace.data.model.sql.SqlDataType;


/**
 * @功能描述：通过JDBC实现数据库操作类
 */
public class JdbcDaoHelper extends JdbcDaoSupport {
	public int[] batchUpdateDataRowBySql(String sql, List<Object[]> batchArgs)
			throws Exception {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		int counts[] = jdbcTemplate.batchUpdate(sql, batchArgs);
		return counts;
	}

	public int updateDataRowBySql(String sql, Object[] params) throws Exception {
		int count = this.getJdbcTemplate().update(sql, params);

		return count;
	}

	/**
	 * 功能描述：通过SQL进行数据库批量修改，并返回主键
	 * @param sql
	 * @param params
	 */
	public List<Long> batchUpdateReturnKey(String sql, List<Object[]> params) throws Exception{
		List<Long> result = new ArrayList<Long>();
		
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
        ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			//写入参数
			for(Object[] param : params) {
				for(int i = 0; i < param.length; i++) {
					ps.setObject(i + 1, param[i]);
				}
				ps.addBatch();//加入批量处理
			}
			//执行
			ps.executeBatch();
			//获取自增主键结果集
			rs = ps.getGeneratedKeys();
			while (rs.next()) {
				result.add(rs.getLong(1));
	        }
		}finally {
			if(rs != null)rs.close();
			if(ps != null)ps.close();
			this.releaseConnection(conn);
		}
		return result;
	}
	
	/**
	 * 调用执行存储过程
	 * 
	 * @param procedureName 存储过程名 如：proc1
	 * @param params 参数列表
	 * @return
	 * @throws SQLException
	 */
	public int callProcedure(String procedureName, List<String[]> params)
			throws SQLException {
		Connection conn = this.getConnection();
		try {
		CallableStatement proc;

		// 存储过程的问好
		String param = "";

		for (int i = 0; i < params.size(); i++) {
			param += "?"+i+",";//jpa style
		}

		param = param.substring(0, param.length() - 1);
		String callName = "{ call " + procedureName + "(" + param + ") }";

		// 组织成调用的存储过程的语句
		proc = conn.prepareCall(callName);

		// 设置存储过程的参数
		for (int i = 0; i < params.size(); i++) {
			String[] para = params.get(i);
			int parameterIndex = i + 1;
			if (para[1] == null) {
				proc.setNull(parameterIndex, Types.VARCHAR);
			} else if (para[0].equalsIgnoreCase("String")) {
				proc.setString(parameterIndex, para[1]);
			} else if (para[0].equalsIgnoreCase("Int")) {
				proc.setInt(parameterIndex, Integer.parseInt(para[1]));
			} else if (para[0].equalsIgnoreCase("double")) {
				proc.setDouble(parameterIndex, Double.parseDouble(para[1]));
			}
		}
		boolean succ = proc.execute();
		return succ == true ? 1 : 0;
		}finally {
			this.releaseConnection(conn);
		}
	}

	/**
	 * 
	 * @方法功能描述:调用存储过程，并用最后一个参数返回一个字符串
	 * @param procedureName
	 * @param params
	 * @param returnParameterIndex
	 * @return
	 * @throws SQLException
	 */
	public String callProcedure(String procedureName, List<String[]> params, int returnParameterIndex) throws SQLException {
		Connection conn = this.getConnection();
		try {
		CallableStatement proc;

		// 存储过程的问好
		String param = "";

		for (int i = 0; i < params.size(); i++) {
			param += "?"+i+",";
		}

		param = param.substring(0, param.length() - 1);
		String callName = "{ call " + procedureName + "(" + param + ") }";

		// 组织成调用的存储过程的语句
		proc = conn.prepareCall(callName);

		// 设置存储过程的参数
		for (int i = 0; i < params.size(); i++) {
			String[] para = params.get(i);
			int parameterIndex = i + 1;
			if (parameterIndex == returnParameterIndex) {
				proc.registerOutParameter(returnParameterIndex, Types.VARCHAR);
			} else if (para[1] == null) {
				proc.setNull(parameterIndex, Types.VARCHAR);
			} else if (para[0].equalsIgnoreCase("String")) {
				proc.setString(parameterIndex, para[1]);
			} else if (para[0].equalsIgnoreCase("Int")) {
				proc.setInt(parameterIndex, Integer.parseInt(para[1]));
			} else if (para[0].equalsIgnoreCase("double")) {
				proc.setDouble(parameterIndex, Double.parseDouble(para[1]));
			}
		}
		proc.execute();

		String result = proc.getString(returnParameterIndex);

		return result;
		}finally {
			this.releaseConnection(conn);
		}
	}

	public List<Map<String,Object>> queryDataRowMapListBySql(String sql) throws Exception {
		List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql);
		return list;
	}

	public List<Object[]> queryDataRowsBySql(String sql) throws Exception {
		List<Object[]> list = new ArrayList<>();
		SqlRowSet srs = this.getJdbcTemplate().queryForRowSet(sql);
		if (srs != null) {
			SqlRowSetMetaData smeta = srs.getMetaData();
			while (srs.next()) {
				Object[] objs = new Object[smeta.getColumnCount()];
				for (int i = 0; i < objs.length; i++) {
					Object val = srs.getObject(i + 1);
					if(val instanceof SerialClob){
						SerialClob clob = (SerialClob)val;
						String val_str = clob.getSubString(1, (int)clob.length());
						objs[i] = val_str;
					}else{
						objs[i] = val;
					}
				}				
				list.add(objs);
			}
		}
		return list;
	} 
	
	public List<Map<String,Object>> queryDataRowMapListBySql(String sql, Object[] params)
			throws Exception {
		List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql, params);

		return list;
	}

	public List<Object[]> queryDataRowsBySql(String sql, Object[] params) throws Exception {
		List<Object[]> list = new ArrayList<>();
		SqlRowSet srs = this.getJdbcTemplate().queryForRowSet(sql, params);
		if (srs != null) {
			SqlRowSetMetaData smeta = srs.getMetaData();

			while (srs.next()) {
				Object[] objs = new Object[smeta.getColumnCount()];
				for (int i = 0; i < objs.length; i++) {
					Object val = srs.getObject(i + 1);
					if(val instanceof SerialClob){
						SerialClob clob = (SerialClob)val;
						String val_str = clob.getSubString(1, (int)clob.length());
						objs[i] = val_str;
					}else{
						objs[i] = val;
					}
				}
				list.add(objs);
			}
		}
		return list;
	}

	public QueryResult<Object[]> queryDataRowPageBySql(String sql, Object[] params, IPagination pagination) throws Exception {
		String countSql = "";
		List<Object[]> list = new ArrayList<>();
		if (pagination.isPaged()) {
			countSql = SqlAdapter.createCountSQL(sql);
			int count = Convert.toInt(this.queryValue(countSql, params), 0);
			pagination.setTotalRows(count);
		}
		// 串接分页SQL
		sql = SqlAdapter.createPageSQL(pagination, sql);
		SqlRowSet srs = this.getJdbcTemplate().queryForRowSet(sql, params);

		if (srs != null) {
			SqlRowSetMetaData smeta = srs.getMetaData();

			while (srs.next()) {
				Object[] objs = new Object[smeta.getColumnCount()];
				for (int i = 0; i < objs.length; i++) {
					objs[i] = srs.getObject(i + 1);
				}
				list.add(objs);
			}
		}
		return new QueryResult<Object[]>(list, pagination);
	}

	/**
	 * 
	 * 功能描述：分页查询，返回对象为List，list中为Map对象
	 *
	 * @param sql
	 * @param params
	 * @param pagination
	 * @return
	 * @throws Exception
	 */
	public QueryResult<Map<String,Object>> queryDataRowMapPageBySql(String sql, Object[] params, IPagination pagination) throws Exception {
		String countSql = "";
		if (pagination.isPaged()) {
			countSql = SqlAdapter.createCountSQL(sql);
			int count = Convert.toInt(this.queryValue(countSql, params), 0);
			pagination.setTotalRows(count);
		}
		// 串接分页SQL
		sql = SqlAdapter.createPageSQL(pagination, sql);
		List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql, params);
		if (!pagination.isPaged()) {
			pagination.setTotalRows(list.size());
		}

		return new QueryResult<Map<String,Object>>(list, pagination);
	}

	/**
	 * 
	 * 功能描述：分页查询，返回对象为List，list中为Map对象
	 * 
	 * @param sql
	 * @param pagination
	 * @return
	 * @throws Exception
	 */
	public QueryResult<Map<String,Object>> queryDataRowMapPageBySql(String sql, IPagination pagination) throws Exception {
		String countSql = "";
		if (pagination.isPaged()) {
			countSql = SqlAdapter.createCountSQL(sql);
			int count = Convert.toInt(this.queryValue(countSql, null), 0);			
			pagination.setTotalRows(count);
		}
		// 串接分页SQL
		sql = SqlAdapter.createPageSQL(pagination, sql);
		List<Map<String,Object>> list = this.getJdbcTemplate().queryForList(sql);
		return new QueryResult<Map<String,Object>>(list, pagination);
	}

	/**
	 * 
	 * 功能描述：分页查询，返回对象为List，list中为Object数组
	 *
	 * @param sql
	 * @param pagination
	 * @return
	 * @throws Exception
	 */
	public QueryResult<Object[]> queryDataRowPageBySql(String sql, IPagination pagination) throws Exception {
		List<Object[]> list = new ArrayList<>();
		String countSql = "";
		if (pagination.isPaged()) {
			countSql = SqlAdapter.createCountSQL(sql);
			int count = this.getJdbcTemplate().queryForObject(countSql, Integer.class);
			pagination.setTotalRows(count);
		}
		// 串接分页SQL
		sql = SqlAdapter.createPageSQL(pagination, sql);
		SqlRowSet srs = this.getJdbcTemplate().queryForRowSet(sql);
		if (srs != null) {
			SqlRowSetMetaData smeta = srs.getMetaData();

			while (srs.next()) {
				Object[] objs = new Object[smeta.getColumnCount()];
				for (int i = 0; i < objs.length; i++) {
					objs[i] = srs.getObject(i + 1);
				}
				list.add(objs);
			}
		}
		return new QueryResult<Object[]>(list, pagination);
	}

	/**返回表字段的数据类型，Map<字段名（小写）, java.sql.Types>
	 * @param tableName
	 * @return
	 * @throws SQLException 
	 */
	public Map<String, SqlDataType> queryColumnsType(String tableName) throws SQLException{
		Connection conn = this.getConnection();
		Map<String, SqlDataType> colTypes = new HashMap<String, SqlDataType>();
		try {
			String sql = "select * from "+tableName+" where 1=2";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			ResultSetMetaData metadata = rs.getMetaData();
			int colCount = metadata.getColumnCount();
			for(int i=1; i<=colCount; ++i){
				SqlDataType sqlDataType = new SqlDataType();
				String colName = metadata.getColumnName(i).toLowerCase();
				int dataType = metadata.getColumnType(i);
				int dataLen = metadata.getColumnDisplaySize(i);
				int precision = metadata.getPrecision(i);
				int scale = metadata.getScale(i);
				sqlDataType.setDataType(dataType);
				sqlDataType.setDataLength(dataLen);
				sqlDataType.setPrecision(precision);
				sqlDataType.setScale(scale);
				colTypes.put(colName, sqlDataType);
			}
			rs.close();
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{		
			this.releaseConnection(conn);	
		}
		return colTypes;
	}
	
	public DataTable queryDataTable(String sql, Object[] params, int pageIndex, int pageSize) throws Exception{
		String countSql = "";
		int totalCount = 0;
		if (pageIndex >0 && pageSize > 0) {
			countSql = SqlAdapter.createCountSQL(sql);
			totalCount = Convert.toInt(this.queryValue(countSql, params), 0);
		}
		// 串接分页SQL
		sql = SqlAdapter.createPageSQL(sql, pageIndex, pageSize);
		Connection conn = this.getConnection();
		PreparedStatement stmt = conn.prepareStatement(sql);
		DataTable table = null;
		try{
			if(params != null && params.length > 0){
				int idx = 1;
				for(Object param : params){
					stmt.setObject(idx, param);
					idx++;
				}
			}
			ResultSet rs = stmt.executeQuery();
			try{
				table = new DataTable(rs);
				table.setPageIndex(pageIndex);
				table.setPageSize(pageSize);
				table.setTotalCount(totalCount);
			}finally{
				rs.close();
			}
		}finally{
			stmt.close();
			this.releaseConnection(conn);
			//conn.close();
		}
		
		return table;
	}
	
	public DataTable queryDataTable(String sql, Object[] params) throws Exception{		
		Connection conn = this.getConnection();
		PreparedStatement stmt = conn.prepareStatement(sql);
		DataTable table = null;
		try{
			if(params != null && params.length > 0){
				int idx = 1;
				for(Object param : params){
					stmt.setObject(idx, param);
					idx++;
				}
			}
			ResultSet rs = stmt.executeQuery();
			try{
				table = new DataTable(rs);				
			}finally{
				rs.close();				
			}
		}finally{
			stmt.close();
			this.releaseConnection(conn);
			//conn.close();
		}
		return table;
	}

	public Clob createClob() throws Exception{
		Connection conn = this.getConnection();
		try {
			Clob clob = conn.createClob();		
			//conn.close(); 不要close， spring自动管理
			return clob;
		}finally {
			this.releaseConnection(conn);
		}
	}
	
	public Object queryValue(String sql) throws Exception{
		List<Object[]> rows = queryDataRowsBySql(sql, null);
		if(rows.size() > 0) {
			Object[] row = rows.get(0);
			return row[0];
		}else return null;
	}
	
	public Object queryValue(String sql, Object[] params) throws Exception{
		List<Object[]> rows = queryDataRowsBySql(sql, params);
		if(rows.size() > 0) {
			Object[] row = rows.get(0);
			return row[0];
		}else return null;
	}

	public void execute(String ddl) {
		this.getJdbcTemplate().execute(ddl);
	}
}